-- Expands the SubSet.Name column to 100 characters.

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE RecordSet.SubSet
	DROP CONSTRAINT FK_RecordSet_SubSet_RecordSetID
GO
ALTER TABLE RecordSet.RecordSet SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE RecordSet.SubSet
	DROP CONSTRAINT DF_RecordSet_SubSet_Open
GO
ALTER TABLE RecordSet.SubSet
	DROP CONSTRAINT DF_RecordSet_SubSet_Active
GO
CREATE TABLE RecordSet.Tmp_SubSet
	(
	ID uniqueidentifier NOT NULL,
	RecordSetID uniqueidentifier NOT NULL,
	Name varchar(100) NOT NULL,
	LastOpenedAt datetime2(7) NULL,
	[Open] bit NOT NULL,
	Active bit NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE RecordSet.Tmp_SubSet SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE RecordSet.Tmp_SubSet ADD CONSTRAINT
	DF_RecordSet_SubSet_Open DEFAULT ((1)) FOR [Open]
GO
ALTER TABLE RecordSet.Tmp_SubSet ADD CONSTRAINT
	DF_RecordSet_SubSet_Active DEFAULT ((1)) FOR Active
GO
IF EXISTS(SELECT * FROM RecordSet.SubSet)
	 EXEC('INSERT INTO RecordSet.Tmp_SubSet (ID, RecordSetID, Name, LastOpenedAt, [Open], Active)
		SELECT ID, RecordSetID, Name, LastOpenedAt, [Open], Active FROM RecordSet.SubSet WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE RecordSet.SubSetGene
	DROP CONSTRAINT FK_RecordSet_SubSetGene_SubSetID
GO
DROP TABLE RecordSet.SubSet
GO
EXECUTE sp_rename N'RecordSet.Tmp_SubSet', N'SubSet', 'OBJECT' 
GO
ALTER TABLE RecordSet.SubSet ADD CONSTRAINT
	PK_RecordSet_SubSet PRIMARY KEY CLUSTERED 
	(
	ID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE UNIQUE NONCLUSTERED INDEX IX_RecordSet_SubSet_Name ON RecordSet.SubSet
	(
	RecordSetID,
	Name
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE RecordSet.SubSet ADD CONSTRAINT
	FK_RecordSet_SubSet_RecordSetID FOREIGN KEY
	(
	RecordSetID
	) REFERENCES RecordSet.RecordSet
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE RecordSet.SubSetGene ADD CONSTRAINT
	FK_RecordSet_SubSetGene_SubSetID FOREIGN KEY
	(
	SubSetID
	) REFERENCES RecordSet.SubSet
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE RecordSet.SubSetGene SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
GO